# 写一个python程序，要求把存放在“C:\Users\Kelvin\Desktop\Python\Excel、Organiation.xlsx”文件
# 中，“Sheet2”表格的“换热站”字段中的内容，按照文本相似性判断，修改为“Sheet1”表格中的“换热站”字段中
# 的内容，并存放在“备注”字段，保留原“Sheet2”表格的“换热站”字段中的内容。
# Welcome to Cursor
# 1. Try generating with command K on a new line. Ask for a pytorch script of a feedforward neural network
# 2. Then, select the outputted code and hit chat. Ask if there's a bug. Ask how to improve.
# 3. Try selecting some code and hitting edit. Ask the bot to add residual layers.
# 4. To try out cursor on your own projects, go to the file menu (top left) and open a folder.
import pandas as pd
import difflib

# read in the excel files
df1 = pd.read_excel(r'C:\Users\Kelvin\Desktop\Python\Excel\Organiation.xlsx', sheet_name='Sheet1')
df2 = pd.read_excel(r'C:\Users\Kelvin\Desktop\Python\Excel\Organiation.xlsx', sheet_name='Sheet2')

# convert the '换热站' column to strings
df1['换热站'] = df1['换热站'].astype(str)
df2['换热站'] = df2['换热站'].astype(str)

# create a new column for the modified '换热站' field
df2['备注'] = ''

# iterate through each row in sheet2
for index, row in df2.iterrows():
    # find the most similar '换热站' value in sheet1
    closest_match = difflib.get_close_matches(row['换热站'], df1['换热站'], n=1)
    # check if the closest_match list is not empty
    if closest_match:
        # update the '换热站' value in sheet2 with the closest match from sheet1
        df2.at[index, '换热站'] = closest_match[0]
    # add the original '换热站' value from sheet2 to the new '备注' column
    df2.at[index, '备注'] = row['换热站']

# save the modified sheet2 as a new excel file
df2.to_excel(r'C:\Users\Kelvin\Desktop\Python\Excel\Modified_Organiation.xlsx', index=False)
